package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.GregorianCalendar;

import budget.Budget;
import contract.EventContract;
import controllers.ExceptionsTypes;

public class EventContractWrapper {
	
	public static ArrayList<EventContract> getAllEventContract(boolean closeConnection) throws Exception{
		ArrayList<EventContract> contracts = new ArrayList<EventContract>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".event_contract ";
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	long valDate = rs.getLong("validity_date");
	        	long stipDate = rs.getLong("stipulate_date");
	        	long idContract = rs.getInt("id_contract");
	        	long idBudget = rs.getInt("id_budget");
	        	GregorianCalendar vDate = new GregorianCalendar();
	        	vDate.setTimeInMillis(valDate);
	        	GregorianCalendar sDate = new GregorianCalendar();
	        	sDate.setTimeInMillis(stipDate);
	        	String clauses = rs.getString("terms");
	        	contracts.add(new EventContract(idContract, sDate, vDate, clauses, BudgetWrapper.getBudget(idBudget, false)));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    return contracts;
	}
	
	public static EventContract getEventContract(long idContract, boolean closeConnection) throws Exception{
		EventContract c = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".event_contract " +
	    		"WHERE id_contract = "+idContract;
	    Statement stmt = DBWrapper.getConnection().createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	    	if(rs.next()){
	        	long valDate = rs.getLong("validity_date");
	        	long stipDate = rs.getLong("stipulate_date");
	        	long idBudget = rs.getInt("id_budget");
	        	GregorianCalendar vDate = new GregorianCalendar();
	        	vDate.setTimeInMillis(valDate);
	        	GregorianCalendar sDate = new GregorianCalendar();
	        	sDate.setTimeInMillis(stipDate);
	        	String clauses = rs.getString("terms");
	        	c = new EventContract(idContract, vDate, sDate, clauses, BudgetWrapper.getBudget(idBudget, false));
	        }
	    stmt.close();
	    if(closeConnection)
	    	DBWrapper.getConnection().close();
	    if(c == null)
	        throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return c;
	}
	
	public static EventContract getEventContractByBudget(Budget budget, boolean closeConnection) throws Exception{
		EventContract c = null;
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".event_contract " +
	    		"WHERE id_budget = "+budget.getId();
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	long idContract = rs.getInt("id_contract");
	        	long valDate = rs.getLong("validity_date");
	        	long stipDate = rs.getLong("stipulate_date");
	        	GregorianCalendar vDate = new GregorianCalendar();
	        	vDate.setTimeInMillis(valDate);
	        	GregorianCalendar sDate = new GregorianCalendar();
	        	sDate.setTimeInMillis(stipDate);
	        	String clauses = rs.getString("terms");
	        	c = new EventContract(idContract, vDate, sDate, clauses, budget);
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    if(c == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return c;
	}
	
	public static void insertEventContract(boolean closeConnection, EventContract ... contracts) throws Exception{
		
		Statement stmt = DBWrapper.getConnection().createStatement();
		for(int i = 0 ; i<contracts.length; i++){
			EventContract c = contracts[i];
			//verifico se la richiesta esiste altrimenti eccezione
			try{
				BudgetWrapper.getBudget(c.getBudget().getId(), false);
			}catch(Exception ex1){
				if(ex1.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
					throw new Exception(ExceptionsTypes.DIPENDENCE_ERROR.name());
			}
			int rows = stmt.executeUpdate(
		        	"INSERT INTO "+ DBWrapper.getDbname() +".event_contract " +
		        	"(id_budget, stipulate_date, validity_date, terms) " +
		            "VALUES(" 
		        	+ c.getBudget().getId() + ", '" 
		        	+ c.getStipulationDate().getTimeInMillis() 
		        	+ "', '"+ c.getValidityDate().getTimeInMillis() 
		        	+"', '"+ c.getClauses() +"')");
			if(rows == 0)
				throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
			c.setId(getEventContractByBudget(c.getBudget(), false).getId());
		}
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	
public static void modifyEventContract(EventContract evnCont, boolean closeConnection) throws Exception{
	
	Statement stmt = DBWrapper.getConnection().createStatement();
	
	EventContractWrapper.getEventContract(evnCont.getId(), false);
	
	int rows = stmt.executeUpdate(
	        "UPDATE "+ DBWrapper.getDbname() +".event_contract" +
	        " SET validity_date=" + evnCont.getValidityDate().getTimeInMillis() +
	        ", stipulate_date=" + evnCont.getStipulationDate().getTimeInMillis() +
	        ", terms='"+evnCont.getClauses() +
	        "' WHERE id_contract=" + evnCont.getId());
	if(rows == 0)
		throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
		stmt.close();
		
		if(closeConnection)
			DBWrapper.getConnection().close();
}

public static void revertEVtoBudget(EventContract evnCont, boolean closeConnection) throws Exception{
		
		String query =  "DELETE "  +
		    			"FROM "+ DBWrapper.getDbname() +".event_contract " +
		    			"WHERE id_contract="+evnCont.getId()+"";
		    
		    	Statement stmt = DBWrapper.getConnection().createStatement();
		        int rows = stmt.executeUpdate(query);
		        if(rows>1){
		        	throw new SQLException();
		        }else if(rows == 0)
		    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
		        stmt.close();
		        if(closeConnection)
		        	DBWrapper.getConnection().close();
	}	

}
